﻿/* Copyright (c) 2011-2012, Zetatron Consulting
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

* Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

* Neither the name of Zetatron Consulting nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AionCxxLibrary.Models;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using AionCxxLibrary.Utilities;
using AionCxxLibrary.Enums;

namespace WorldServer.DAO
{
    public class CharacterDAO
    {
        public static List<int> SelectCharacterIDsByAccount(int accountId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT ID FROM dbo.Characters WHERE AccountID = @AcctID", con);
            cmd.Parameters.Add("@AcctID", SqlDbType.Int).Value = accountId;
            Log.Query(cmd);
            List<int> Characters = new List<int>();
            try
            {
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    int CharID = rdr.GetInt32(0);
                    Characters.Add(CharID);
                }
            }
            catch (Exception e)
            {
                Log.Error("Cannot load characters for account " + accountId + "!", e);
                Characters.Clear();
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return Characters;
        }

        public static TCharacter SelectCharacterTemplate(int characterId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT Name,AccountID,Class,Race,Lvl,Experience,ExperienceRecoverable,Gender,Title,WorldID,Note FROM dbo.Characters where ID = @CharID", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            TCharacter Char = null;
            Log.Query(cmd);
            try
            {
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    Char = new TCharacter();
                    Char.ID = characterId;
                    Char.Name = rdr["Name"].ToString();
                    Char.AccountID = int.Parse(rdr["AccountID"].ToString());
                    Char.Class = (CharacterClass)int.Parse(rdr["Class"].ToString());
                    Char.Race = (Race)int.Parse(rdr["Race"].ToString());
                    Char.Level = byte.Parse(rdr["Lvl"].ToString());
                    Char.Experience = long.Parse(rdr["Experience"].ToString());
                    Char.ExperienceRecoverable = long.Parse(rdr["ExperienceRecoverable"].ToString());
                    Char.Gender = byte.Parse(rdr["Gender"].ToString());
                    Char.Title = byte.Parse(rdr["Title"].ToString());
                    Char.WorldID = int.Parse(rdr["WorldID"].ToString());
                    Char.Note = rdr["Note"] == null ? "" : rdr["Note"].ToString();
                }
            }
            catch (Exception e)
            {
                Log.Error("Cannot load character template for " + characterId + "!", e);
                Char = null;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return Char;
        }

        public static Vector4D SelectPositionByCharacterID(int characterId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT x, y, z, Heading FROM dbo.CharactersPositions WHERE CharacterID = @CharID", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            Vector4D result = null;
            Log.Query(cmd);
            try
            {
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                    result = new Vector4D(
                        float.Parse(rdr["x"].ToString()),
                        float.Parse(rdr["y"].ToString()),
                        float.Parse(rdr["z"].ToString()),
                        byte.Parse(rdr["Heading"].ToString()));
            }
            catch (Exception e)
            {
                Log.Error("Cannot load position for " + characterId + "!", e);
                result = null;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }

        public static ushort SelectChannelID(int characterId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT ChannelID FROM dbo.CharactersPositions WHERE CharacterID = @CharID", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            ushort result = 0;
            Log.Query(cmd);
            try
            {
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                    result = ushort.Parse(rdr["ChannelID"].ToString());
            }
            catch (Exception e)
            {
                Log.Error("Cannot load channel ID for " + characterId + "!", e);
                result = 0;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }

        public static TCharacterAppearance SelectAppearanceByCharacterId(int characterId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT Voice, SkinColor, HairColor, EyeColor, LipColor, Height, Face, Hair, Decoration, Tattoo, FaceContour, " +
                "Expression, Jawline, Forehead, EyeHeight, EyeSpace, EyeWidth, EyeSize, EyeShape, EyeAngle, BrowHeight, BrowAngle, BrowShape, Nose, NoseBridge, NoseWidth, NoseTip, Cheek, LipHeight, " +
                "MouthSize, LipSize, Smile, LipShape, JawHeight, ChinJut, EarShape, HeadSize, Neck, NeckLength, ShoulderSize, Torso, Chest, Waist, Hips, ArmThickness, HandSize, LegThickness, LegLength, " +
                "FacialRate, ArmLength, FeetSize, Shoulders, FaceShape FROM dbo.CharactersAppearance WHERE CharacterID = @CharID", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            TCharacterAppearance app = null;
            Log.Query(cmd);
            try
            {
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    app = new TCharacterAppearance();
                    app.Voice = rdr.GetByte(0);
                    app.RGB_Skin = rdr.GetInt32(1);
                    app.RGB_Hair = rdr.GetInt32(2);
                    app.RGB_Eye = rdr.GetInt32(3);
                    app.RGB_Lip = rdr.GetInt32(4);
                    app.Height = float.Parse(rdr["Height"].ToString());
                    app.Face = byte.Parse(rdr["Face"].ToString());
                    app.Hair = byte.Parse(rdr["Hair"].ToString());
                    app.Decoration = byte.Parse(rdr["Decoration"].ToString());
                    app.Tattoo = byte.Parse(rdr["Tattoo"].ToString());
                    app.FaceContour = byte.Parse(rdr["FaceContour"].ToString());
                    app.Expression = byte.Parse(rdr["Expression"].ToString());
                    app.Jawline = byte.Parse(rdr["Jawline"].ToString());
                    app.Forehead = byte.Parse(rdr["Forehead"].ToString());
                    app.EyeHeight = byte.Parse(rdr["EyeHeight"].ToString());
                    app.EyeSpace = byte.Parse(rdr["EyeSpace"].ToString());
                    app.EyeWidth = byte.Parse(rdr["EyeWidth"].ToString());
                    app.EyeSize = byte.Parse(rdr["EyeSize"].ToString());
                    app.EyeShape = byte.Parse(rdr["EyeShape"].ToString());
                    app.EyeAngle = byte.Parse(rdr["EyeAngle"].ToString());
                    app.BrowHeight = byte.Parse(rdr["BrowHeight"].ToString());
                    app.BrowAngle = byte.Parse(rdr["BrowAngle"].ToString());
                    app.BrowShape = byte.Parse(rdr["BrowShape"].ToString());
                    app.NoseBridge = byte.Parse(rdr["NoseBridge"].ToString());
                    app.NoseWidth = byte.Parse(rdr["NoseWidth"].ToString());
                    app.NoseTip = byte.Parse(rdr["NoseTip"].ToString());
                    app.Cheek = byte.Parse(rdr["Cheek"].ToString());
                    app.LipHeight = byte.Parse(rdr["LipHeight"].ToString());
                    app.MouthSize = byte.Parse(rdr["MouthSize"].ToString());
                    app.LipSize = byte.Parse(rdr["LipSize"].ToString());
                    app.Smile = byte.Parse(rdr["Smile"].ToString());
                    app.LipShape = byte.Parse(rdr["LipShape"].ToString());
                    app.JawHeight = byte.Parse(rdr["JawHeight"].ToString());
                    app.ChinJut = byte.Parse(rdr["ChinJut"].ToString());
                    app.EarShape = byte.Parse(rdr["EarShape"].ToString());
                    app.HeadSize = byte.Parse(rdr["HeadSize"].ToString());
                    app.Neck = byte.Parse(rdr["Neck"].ToString());
                    app.NeckLength = byte.Parse(rdr["NeckLength"].ToString());
                    app.ShoulderSize = byte.Parse(rdr["ShoulderSize"].ToString());
                    app.Torso = byte.Parse(rdr["Torso"].ToString());
                    app.Chest = byte.Parse(rdr["Chest"].ToString());
                    app.Waist = byte.Parse(rdr["Waist"].ToString());
                    app.Hips = byte.Parse(rdr["Hips"].ToString());
                    app.ArmThickness = byte.Parse(rdr["ArmThickness"].ToString());
                    app.HandSize = byte.Parse(rdr["HandSize"].ToString());
                    app.LegThickness = byte.Parse(rdr["LegThickness"].ToString());
                    app.LegLength = byte.Parse(rdr["LegLength"].ToString());
                    app.FacialRate = byte.Parse(rdr["FacialRate"].ToString());
                    app.ArmLength = byte.Parse(rdr["ArmLength"].ToString());
                    app.FeetSize = byte.Parse(rdr["FeetSize"].ToString());
                    app.Shoulders = byte.Parse(rdr["Shoulders"].ToString());
                    app.FaceShape = byte.Parse(rdr["FaceShape"].ToString());
                }
            }
            catch (Exception e)
            {
                Log.Error("Cannot load appearance for " + characterId + "!", e);
                app = null;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return app;
        }

        public static bool CheckNameExists(string name)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT COUNT(ID) FROM dbo.Characters WHERE Name = @Name", con);
            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = name;
            Log.Query(cmd);
            bool result = true;
            try
            {
                con.Open();
                result = int.Parse(cmd.ExecuteScalar().ToString()) > 0;
            }
            catch (Exception e)
            {
                result = true;
                Log.Error("Cannot check whether name exists!", e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }

        public static int SelectCharacterCountByAccount(int accountId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT COUNT(ID) FROM dbo.Characters WHERE AccountID = @AcctID", con);
            cmd.Parameters.Add("@AcctID", SqlDbType.Int).Value = accountId;
            int result = 0;
            Log.Query(cmd);
            try
            {
                con.Open();
                result = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch (Exception e)
            {
                result = 0;
                Log.Error("Cannot get characters count for " + accountId + "!", e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return result;
        }

        static object CharacterCreationMutex = new object();

        public static bool InsertCharacter(TCharacter character)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd1 = new SqlCommand("INSERT INTO dbo.Characters(Name, AccountID, Class, Race, Lvl, Experience, ExperienceRecoverable, Gender, Title, WorldID)" +
                "VALUES(@Name, @AcctID, @Class, @Race, @Level, @Experience, @ExperienceRecoverable, @Gender, @Title, @WorldID); SELECT @@IDENTITY;", con);
            cmd1.Parameters.Add("@Name", SqlDbType.VarChar).Value = character.Name;
            cmd1.Parameters.Add("@AcctID", SqlDbType.BigInt).Value = character.AccountID;
            cmd1.Parameters.Add("@Class", SqlDbType.TinyInt).Value = (int)character.Class;
            cmd1.Parameters.Add("@Race", SqlDbType.TinyInt).Value = (int)character.Race;
            cmd1.Parameters.Add("@Level", SqlDbType.TinyInt).Value = character.Level;
            cmd1.Parameters.Add("@Experience", SqlDbType.BigInt).Value = character.Experience;
            cmd1.Parameters.Add("@ExperienceRecoverable", SqlDbType.BigInt).Value = character.ExperienceRecoverable;
            cmd1.Parameters.Add("@Gender", SqlDbType.TinyInt).Value = (int)character.Gender;
            cmd1.Parameters.Add("@Title", SqlDbType.TinyInt).Value = character.Title;
            cmd1.Parameters.Add("@WorldID", SqlDbType.Int).Value = character.WorldID;
            SqlCommand cmd2 = new SqlCommand("INSERT INTO dbo.CharactersAppearance(CharacterID, Voice, SkinColor, HairColor, EyeColor, LipColor, Height, Face, Hair, Decoration, Tattoo, FaceContour, " +
                "Expression, Jawline, ForeHead, EyeHeight, EyeSpace, EyeWidth, EyeSize, EyeShape, EyeAngle, BrowHeight, BrowAngle, BrowShape, Nose, NoseBridge, NoseWidth, NoseTip, Cheek, LipHeight, " +
                "MouthSize, LipSize, Smile, LipShape, JawHeight, ChinJut, EarShape, HeadSize, Neck, NeckLength, ShoulderSize, Torso, Chest, Waist, Hips, ArmThickness, HandSize, LegThickness, LegLength, " +
                "FacialRate, ArmLength, FeetSize, Shoulders, FaceShape) VALUES(@CharID, @Voice, @SkinColor, @HairColor, @EyeColor, @LipColor, @Height, @Face, @Hair, @Decoration, @Tattoo, @FaceContour, " +
                "@Expression, @Jawline, @ForeHead, @EyeHeight, @EyeSpace, @EyeWidth, @EyeSize, @EyeShape, @EyeAngle, @BrowHeight, @BrowAngle, @BrowShape, @Nose, @NoseBridge, @NoseWidth, @NoseTip, " +
                "@Cheek, @LipHeight, @MouthSize, @LipSize, @Smile, @LipShape, @Jawheight, @ChinJut, @EarShape, @HeadSize, @Neck, @NeckLength, @ShoulderSize, @Torso, @Chest, @Waist, @Hips, " +
                "@ArmThickness, @HandSize, @LegThickness, @LegLength, @FacialRate, @ArmLength, @FeetSize, @Shoulders, @FaceShape)", con);
            cmd2.Parameters.Add("@Voice", SqlDbType.TinyInt).Value = character.Appearance.Voice;
            cmd2.Parameters.Add("@SkinColor", SqlDbType.Int).Value = character.Appearance.RGB_Skin;
            cmd2.Parameters.Add("@HairColor", SqlDbType.Int).Value = character.Appearance.RGB_Hair;
            cmd2.Parameters.Add("@EyeColor", SqlDbType.Int).Value = character.Appearance.RGB_Eye;
            cmd2.Parameters.Add("@LipColor", SqlDbType.Int).Value = character.Appearance.RGB_Lip;
            cmd2.Parameters.Add("@Height", SqlDbType.Float).Value = character.Appearance.Height;
            cmd2.Parameters.Add("@Face", SqlDbType.TinyInt).Value = character.Appearance.Face;
            cmd2.Parameters.Add("@Hair", SqlDbType.TinyInt).Value = character.Appearance.Hair;
            cmd2.Parameters.Add("@Decoration", SqlDbType.TinyInt).Value = character.Appearance.Decoration;
            cmd2.Parameters.Add("@Tattoo", SqlDbType.TinyInt).Value = character.Appearance.Tattoo;
            cmd2.Parameters.Add("@FaceContour", SqlDbType.TinyInt).Value = character.Appearance.FaceContour;
            cmd2.Parameters.Add("@Expression", SqlDbType.TinyInt).Value = character.Appearance.Expression;
            cmd2.Parameters.Add("@Jawline", SqlDbType.TinyInt).Value = character.Appearance.Jawline;
            cmd2.Parameters.Add("@ForeHead", SqlDbType.TinyInt).Value = character.Appearance.Forehead;
            cmd2.Parameters.Add("@EyeHeight", SqlDbType.TinyInt).Value = character.Appearance.EyeHeight;
            cmd2.Parameters.Add("@EyeSpace", SqlDbType.TinyInt).Value = character.Appearance.EyeSpace;
            cmd2.Parameters.Add("@EyeWidth", SqlDbType.TinyInt).Value = character.Appearance.EyeWidth;
            cmd2.Parameters.Add("@EyeSize", SqlDbType.TinyInt).Value = character.Appearance.EyeSize;
            cmd2.Parameters.Add("@EyeShape", SqlDbType.TinyInt).Value = character.Appearance.EyeShape;
            cmd2.Parameters.Add("@EyeAngle", SqlDbType.TinyInt).Value = character.Appearance.EyeAngle;
            cmd2.Parameters.Add("@BrowHeight", SqlDbType.TinyInt).Value = character.Appearance.BrowHeight;
            cmd2.Parameters.Add("@BrowAngle", SqlDbType.TinyInt).Value = character.Appearance.BrowAngle;
            cmd2.Parameters.Add("@BrowShape", SqlDbType.TinyInt).Value = character.Appearance.BrowShape;
            cmd2.Parameters.Add("@Nose", SqlDbType.TinyInt).Value = character.Appearance.Nose;
            cmd2.Parameters.Add("@NoseBridge", SqlDbType.TinyInt).Value = character.Appearance.NoseBridge;
            cmd2.Parameters.Add("@NoseWidth", SqlDbType.TinyInt).Value = character.Appearance.NoseWidth;
            cmd2.Parameters.Add("@NoseTip", SqlDbType.TinyInt).Value = character.Appearance.NoseTip;
            cmd2.Parameters.Add("@Cheek", SqlDbType.TinyInt).Value = character.Appearance.Cheek;
            cmd2.Parameters.Add("@LipHeight", SqlDbType.TinyInt).Value = character.Appearance.LipHeight;
            cmd2.Parameters.Add("@MouthSize", SqlDbType.TinyInt).Value = character.Appearance.MouthSize;
            cmd2.Parameters.Add("@LipSize", SqlDbType.TinyInt).Value = character.Appearance.LipSize;
            cmd2.Parameters.Add("@Smile", SqlDbType.TinyInt).Value = character.Appearance.Smile;
            cmd2.Parameters.Add("@LipShape", SqlDbType.TinyInt).Value = character.Appearance.LipShape;
            cmd2.Parameters.Add("@JawHeight", SqlDbType.TinyInt).Value = character.Appearance.JawHeight;
            cmd2.Parameters.Add("@ChinJut", SqlDbType.TinyInt).Value = character.Appearance.ChinJut;
            cmd2.Parameters.Add("@EarShape", SqlDbType.TinyInt).Value = character.Appearance.EarShape;
            cmd2.Parameters.Add("@HeadSize", SqlDbType.TinyInt).Value = character.Appearance.HeadSize;
            cmd2.Parameters.Add("@Neck", SqlDbType.TinyInt).Value = character.Appearance.Neck;
            cmd2.Parameters.Add("@NeckLength", SqlDbType.TinyInt).Value = character.Appearance.NeckLength;
            cmd2.Parameters.Add("@ShoulderSize", SqlDbType.TinyInt).Value = character.Appearance.ShoulderSize;
            cmd2.Parameters.Add("@Torso", SqlDbType.TinyInt).Value = character.Appearance.Torso;
            cmd2.Parameters.Add("@Chest", SqlDbType.TinyInt).Value = character.Appearance.Chest;
            cmd2.Parameters.Add("@Waist", SqlDbType.TinyInt).Value = character.Appearance.Waist;
            cmd2.Parameters.Add("@Hips", SqlDbType.TinyInt).Value = character.Appearance.Hips;
            cmd2.Parameters.Add("@ArmThickness", SqlDbType.TinyInt).Value = character.Appearance.ArmThickness;
            cmd2.Parameters.Add("@HandSize", SqlDbType.TinyInt).Value = character.Appearance.HandSize;
            cmd2.Parameters.Add("@LegThickness", SqlDbType.TinyInt).Value = character.Appearance.LegThickness;
            cmd2.Parameters.Add("@LegLength", SqlDbType.TinyInt).Value = character.Appearance.LegLength;
            cmd2.Parameters.Add("@FacialRate", SqlDbType.TinyInt).Value = character.Appearance.FacialRate;
            cmd2.Parameters.Add("@ArmLength", SqlDbType.TinyInt).Value = character.Appearance.ArmLength;
            cmd2.Parameters.Add("@FeetSize", SqlDbType.TinyInt).Value = character.Appearance.FeetSize;
            cmd2.Parameters.Add("@Shoulders", SqlDbType.TinyInt).Value = character.Appearance.Shoulders;
            cmd2.Parameters.Add("@FaceShape", SqlDbType.TinyInt).Value = character.Appearance.FaceShape;
            SqlCommand cmd3 = new SqlCommand("INSERT INTO dbo.CharactersPositions(CharacterID, x, y, z, Heading) VALUES(@CharID, @x, @y, @z, @h)", con);
            cmd3.Parameters.Add("@x", SqlDbType.Float).Value = character.Position.x;
            cmd3.Parameters.Add("@y", SqlDbType.Float).Value = character.Position.y;
            cmd3.Parameters.Add("@z", SqlDbType.Float).Value = character.Position.z;
            cmd3.Parameters.Add("@h", SqlDbType.TinyInt).Value = character.Position.h;
            bool result = false;
            SqlTransaction transaction = null;
            try
            {
                lock (CharacterCreationMutex)
                {
                    con.Open();
                    transaction = con.BeginTransaction();
                    cmd1.Transaction = transaction;
                    cmd2.Transaction = transaction;
                    cmd3.Transaction = transaction;
                    Log.Query(cmd1);
                    object res1 = cmd1.ExecuteScalar();
                    if (res1 != null)
                    {
                        int characterId = int.Parse(res1.ToString());
                        cmd2.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
                        cmd3.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
                        Log.Query(cmd2);
                        cmd2.ExecuteNonQuery();
                        Log.Query(cmd3);
                        cmd3.ExecuteNonQuery();
                        character.ID = characterId;
                        transaction.Commit();
                        result = true;
                    }
                }
            }
            catch (Exception e)
            {
                Log.Error("Cannot create character!", e);
                result = false;
                if (transaction != null)
                    transaction.Rollback();
            }
            finally
            {
                cmd1.Dispose();
                con.Close();
            }
            return result;
        }

        public static byte[] SelectSettings(int characterId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT Settings FROM dbo.CharacterSettings WHERE CharacterID = @CharID", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            Log.Query(cmd);
            byte[] res = null;
            try
            {
                con.Open();
                object result = cmd.ExecuteScalar();
                if (result != null && result != DBNull.Value)
                {
                    res = (byte[])result;
                }
            }
            catch (Exception e)
            {
                Log.Error("Cannot select settings for " + characterId, e);
                res = null;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return res;
        }

        public static byte[] SelectQuickBars(int characterId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("SELECT QuickBars FROM dbo.CharacterSettings WHERE CharacterID = @CharID", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            Log.Query(cmd);
            byte[] res = null;
            try
            {
                con.Open();
                object result = cmd.ExecuteScalar();
                if (result != null && result != DBNull.Value)
                {
                    res = (byte[])result;
                }
            }
            catch (Exception e)
            {
                Log.Error("Cannot select quickbars for " + characterId, e);
                res = null;
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
            return res;
        }

        public static void InsertSettingsNull(int characterId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("INSERT INTO dbo.CharacterSettings(CharacterID, Settings, QuickBars) VALUES(@CharID, NULL, NULL)", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            Log.Query(cmd);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.Error("Cannot insert null settings for " + characterId, e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        }

        public static void UpdateSettings(int characterId, byte type, byte[] data)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("UPDATE dbo.CharacterSettings SET " + (type == 0 ? "Settings" : "QuickBars") + " = @data WHERE CharacterID = @CharID", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            cmd.Parameters.Add("@data", SqlDbType.VarBinary).Value = data;
            Log.Query(cmd);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.Error("Cannot update " + (type == 0 ? "Settings" : "QuickBars") + " for " + characterId, e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        }

        public static void DeleteCharacter(int characterID)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("DELETE FROM dbo.Characters WHERE ID = @CharID", con);
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterID;
            // add command params here
            Log.Query(cmd);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.Error("Cannot delete character " + characterID, e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        }

        public static void UpdateClass(int characterId, byte clazz)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("UPDATE dbo.Characters SET Class = @Cl WHERE ID = @CharID", con);
            cmd.Parameters.Add("@Cl", SqlDbType.TinyInt).Value = clazz;
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            // add command params here
            Log.Query(cmd);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.Error("Cannot update player class to " + clazz + " for " + characterId, e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        }

        public static void UpdateLevelAndExp(int characterId, long exp, long expRecoverable, byte level)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["WorldCatalog"].ToString());
            SqlCommand cmd = new SqlCommand("UPDATE dbo.Characters SET Experience = @Exp, ExperienceRecoverable = @ExpRecov, Lvl = @Lvl WHERE ID = @CharID", con);
            cmd.Parameters.Add("@Exp", SqlDbType.BigInt).Value = exp;
            cmd.Parameters.Add("@ExpRecov", SqlDbType.BigInt).Value = expRecoverable;
            cmd.Parameters.Add("@Lvl", SqlDbType.TinyInt).Value = level;
            cmd.Parameters.Add("@CharID", SqlDbType.Int).Value = characterId;
            // add command params here
            Log.Query(cmd);
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log.Error("Cannot update EXP/Level for " + characterId, e);
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        }

    }
}
